Table Calculations

Table Calculations generate new columns of data based on predefined aggregate functions executed against existing columns of data. Any data column added to the 'Values' or 'Other Values' shelves in the Chart Builder UI can be used as a source column for a table calculation. The Table Calculations UI contains the following fields:

1. Column - Lists all columns added to the “Values” and “Other Values” shelves.

2. Calculation Type - The following classes of calculation are available in the product:

   • Running Calculations
     • Running Sum (RSUM)
     • Running Average (RAVG)
     • Running Min (RMIN)
     • Running Max (RMAX)
   • Difference Calculations
     • Difference
     • %Difference

3. Level - This option appears when you have multiple groups (more than 1) to determine 'at what level' you are doing your calculation. For example, if you have grouped sales by year and quarter, you can calculate for each year and restart when you get to the next year, or calculate the sales for the entire table. The default level is Table.

4. Custom Label - Add a custom name to the calculation for identification (the name is shown only in the panel).

5. Remove - Delete the table calculation.

   

Note: Running Calculations calculate the running aggregate of a value that can optionally be calculated for any number of grouped categories. The calculations are affected by the sort order of the data.

Qrvey supports the following table calculations in Bar, Line, Multiseries, Combo Charts and Heatmap.

Running Calculations

Qrvey supports the following running calculations:

Running Sum

Running Sum (RSUM) calculates the new sum total value by adding the value in the source column of the current row to the computed sum total from the previous row.

For example, if the last computed sum total was 100 and the value of the source column in the current row is 25, then the new sum total will be 125.

Running Average

Running Average (RAVG) calculates the new average by referencing the value in the source column of the current row and computing the new average based on the average from the previous row.

For example, if the average from the first row of data was 100 and the value of the source column in the second row of data is 25, then the new computed average will be 62.5.

The following table shows how Running Average is calculated.

Values	Running Average
10	10 (10/1)
20	15 (30/2)
30	20 (60/3)
40	25 (100/4)
50	30 (150/5)

The following image displays the use of a Running Average.

Running Minimum

The Running Minimum function (RMIN) references the value in the current row from the source data column and compares it to the previous minimum value. If the value in the source column is less than the previously computed minimum value, then the new running minimum value is updated with the new lower value. For example, if the value in one row is '27', and the one in the second row is '20', the new minimum is '20'. If the value in the third row is '18' then the new minimum is '18'.

The following example shows how Running Minimum is calculated.

Values	Running Minimum
8	8
6	6 (6 < 8)
10	6 (6 < 10)
9	6 (6 < 9)
5	5 (5 < 6)

The following image displays the use of a Running Minimum.

Running Maximum

The Running Maximum function (RMAX) references the value in the current row from the source data column and compares it to the previous maximum value. If the value in the source column is greater than the previously computed maximum value, then the new running maximum value is updated with the new higher value. For example, if the value in one row is “27”, and the one in the second row is “20”, the maximum shown is “27”; if the value in the third row is “35” then the new maximum is “35”.

The following example shows how Running Maximum is calculated.

Values	Running Maximum
8	8
6	8 (8 > 6)
10	10 (10 > 8)
9	10 (10 > 9)
5	10 (10 > 5)

The following image displays the use of a Running Maximum.

Create a Running Calculation

Table calculations can be added to charts in the following ways:

• Using the chart builder configuration panel and selecting General followed by new Table Calculations option and finally selecting the + Add Table Calculation.

  

• Dragging and dropping numeric columns to Values or Other Values shelves and selecting Table Calculations in the options (three-dot) menu.

  

If the data contains null values, the calculation ignores the empty records. Any event that causes a chart to reload also causes the table calculations to be computed again. Examples include the following:

• Sort order changes.
• Filters applied.
• System receives new data.
• Creator changes the max data point value.

Qrvey generally describes these running calculations as Running_[sum, avg, min, max].